package xutils

import (
	"encoding/csv"
	"errors"
	"fmt"
	"github.com/spf13/cast"
	"github.com/xuri/excelize/v2"
	"os"
	"path/filepath"
)

type ExcelHeader struct {
	Index int
	Title string
}

func CreateExcel(filename string, data [][]string) error {
	if len(data) == 0 {
		return errors.New("empty data")
	}

	ext := filepath.Ext(filename)
	if ext == ".csv" {
		fh, err := os.OpenFile(filename, os.O_CREATE|os.O_RDWR, 0777)
		if err != nil {
			return err
		}
		defer fh.Close()
		return csv.NewWriter(fh).WriteAll(data)
	}

	fh := CreateExcelFileWithData(data)
	defer fh.Close()
	return fh.SaveAs(filename)
}

// CreateExcelFileWithData 生成*excelize.File
func CreateExcelFileWithData[T any](data [][]T) *excelize.File {
	fh := excelize.NewFile()
	list := fh.GetSheetList()
	name := list[0]
	i := 0
	for _, v := range data {
		if len(data) == 0 {
			continue
		}
		i++
		for index, vv := range v {
			fh.SetCellValue(
				name,
				fmt.Sprintf("%s%d", index2Char(index), i),
				vv,
			)
		}
	}

	return fh
}

// CreateExcelFileWithHeader 生成*excelize.File，通过设置头部字段来实现，头部和数据进行分离
func CreateExcelFileWithHeader(headers map[string]*ExcelHeader, rows []map[string]any) *excelize.File {
	f := excelize.NewFile()
	sheetName := "Sheet1"
	index, _ := f.NewSheet(sheetName)
	f.SetActiveSheet(index)
	// 填充单元格
	// 写头
	for _, v := range headers {
		axis := formatExcelAxis(1, v.Index)
		f.SetCellValue(sheetName, axis, v.Title)
	}
	// 写内容
	for r, v := range rows {
		for k, vv := range v {
			if header, ok := headers[k]; ok {
				axis := formatExcelAxis(r+2, header.Index)
				f.SetCellValue(sheetName, axis, vv)
			}
		}
	}
	return f
}

func index2Char(index int) string {
	r := make([]byte, 0)
	r = append(r, byte(index%26+65))
	index = index / 26
	for index > 0 {
		index = index - 1
		r = append([]byte{byte(index%26 + 65)}, r...)
		index = index / 26
	}

	return string(r)
}

// GetExcelRows 获取xlsx文件或csv文件的数据
// sheet sheet下标，从0开始计算
// headerIndex 头部字段名指定行，一般为1
// startIndex 数据起始行
// 不建议读大文件
func GetExcelRows(filename string, sheet, headerIndex, startIndex int) ([]map[string]string, error) {
	fh, err := os.Open(filename)
	if err != nil {
		return nil, err
	}
	defer fh.Close()
	rows := make([][]string, 0)
	if filepath.Ext(filename) == ".csv" {
		rows, err = csv.NewReader(fh).ReadAll()
	} else {
		efh, err := excelize.OpenReader(fh)
		if err != nil {
			return nil, err
		}
		defer efh.Close()
		rows, err = efh.GetRows(efh.GetSheetName(sheet))
	}
	head := rows[headerIndex-1]
	list := make([]map[string]string, 0)
	for i, v := range rows {
		if i == headerIndex-1 || i < startIndex-1 {
			continue
		}
		item := make(map[string]string)
		for ii, vv := range v {
			item[head[ii]] = vv
		}
		item["ROW_INDEX"] = cast.ToString(i + 1)
		list = append(list, item)
	}
	return list, nil
}

func GetExcelSliceRows(filename string, sheet int) ([][]string, error) {
	fh, err := excelize.OpenFile(filename)
	if err != nil {
		return nil, err
	}
	defer fh.Close()
	return fh.GetRows(fh.GetSheetName(sheet))
}

func formatExcelAxis(r, index int) string {
	b := index2Char(index)
	return fmt.Sprintf("%s%d", b, r)
}
